package com.note.dao.note;

import com.note.pojo.Note;
import com.note.pojo.Notetype;
import com.note.pojo.notePageShow;
import com.note.utils.BaseCC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class noteDaoImpl implements noteDao{
    @Override
    public boolean write(String title, String content, int typeId, Timestamp pubtime, int userId) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into note(title, content, typeid, pubtime, user_id, Visits) VALUES(?,?,?,?,?,0)";
        Object[] params = {title, content, typeId, pubtime, userId};
        int result = BaseCC.execute(conn, sql, params, ps);
        BaseCC.closeResource(conn, ps, null);
        if(result>0)
            return true;
        else
            return false;
    }

    @Override
    public int all() throws SQLException {
        Connection conn = BaseCC.getConnection();
        ResultSet rs = null;
        String sql = "select count(1) as totalData from note";
        rs = BaseCC.execute(conn, sql, null, null, rs);
        int totalData=0;
        if(rs.next()){
            totalData = rs.getInt("totalData");
        }
        BaseCC.closeResource(conn, null, rs);
        return totalData;
    }

    @Override
    public List<notePageShow> showAllNote(int beginPage, int pageSize) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select n.note_id,n.title,u.username,t.typename,n.pubtime,n.Visits from note n, users u, notetype t where n.user_id=u.id and n.typeid=t.typeid LIMIT ?,?";
        Object[] params = {beginPage, pageSize};
        List<notePageShow> notes = new ArrayList<>();
        rs = BaseCC.execute(conn, sql, params, ps, rs);
        while(rs.next()){
            notePageShow note = new notePageShow();
            note.setNoteId(rs.getInt("note_id"));
            note.setTitle(rs.getString("title"));
            note.setTypename(rs.getString("typename"));
            note.setUsername(rs.getString("username"));
            note.setPubTime(rs.getTimestamp("pubtime"));
            note.setVisits(rs.getInt("Visits"));
            notes.add(note);
        }
        BaseCC.closeResource(conn, ps, rs);
        return notes;
    }

    @Override
    public int deleteNote(int noteId) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        String sql = "delete from note where note_id=?";
        Object[] params = {noteId};
        int row = BaseCC.execute(conn, sql, params, ps);
        BaseCC.closeResource(conn, ps, null);
        return row;
    }

    @Override
    public Note selectOne(int noteId) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from note where note_id=?";
        Object[] params = {noteId};
        rs = BaseCC.execute(conn, sql, params, ps, rs);
        Note note = new Note();
        if (rs.next()){
            note.setNoteId(rs.getInt("note_id"));
            note.setContent(rs.getString("content"));
            note.setTitle(rs.getString("title"));
            note.setTypeid(rs.getInt("typeid"));
            note.setPubtime(rs.getTimestamp("pubtime"));
            note.setUserId(rs.getInt("user_id"));
            note.setVisits(rs.getInt("Visits"));
        }
        BaseCC.closeResource(conn, ps, rs);
        return note;
    }

    @Override
    public int updateNote(String title, String content, int typeId, int noteId) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        String sql = "update note set title=?,content=?,typeid=? where note_id=?";
        Object[] params = {title, content, typeId, noteId};
        int row = BaseCC.execute(conn, sql, params, ps);
        BaseCC.closeResource(conn, ps, null);
        return row;
    }

    //获取类型列表
    @Override
    public List<Notetype> getTypeList() throws SQLException {
        Connection conn = BaseCC.getConnection();
        ResultSet rs = null;
        String sql = "select * from notetype";
        List<Notetype> typeList = new ArrayList<>();
        rs = BaseCC.execute(conn, sql, null, null ,rs);
        while(rs.next()){
            Notetype type = new Notetype();
            type.setTypeid(rs.getInt("typeid"));
            type.setTypename(rs.getString("typename"));
            typeList.add(type);
        }
        BaseCC.closeResource(conn,null,rs);
        return typeList;
    }

    @Override
    public List<notePageShow> getNoteByType(int typeId) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps =null;
        ResultSet rs = null;
        String sql = "select n.note_id,n.title,u.username,t.typename,n.pubtime,n.Visits from note n, users u, notetype t where n.user_id=u.id and n.typeid=t.typeid and t.typeid=?";
        Object[] params = {typeId};
        rs = BaseCC.execute(conn, sql, params, ps, rs);
        List<notePageShow> noteList = new ArrayList<>();
        while(rs.next()){
            notePageShow note = new notePageShow();
            note.setNoteId(rs.getInt("note_id"));
            note.setTitle(rs.getString("title"));
            note.setTypename(rs.getString("typename"));
            note.setUsername(rs.getString("username"));
            note.setPubTime(rs.getTimestamp("pubtime"));
            note.setVisits(rs.getInt("visits"));
            noteList.add(note);
        }
        BaseCC.closeResource(conn,ps,rs);
        return noteList;
    }

    @Override
    public List<notePageShow> getNoteBytitle(String title) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps =null;
        ResultSet rs = null;
        String sql = "select n.note_id,n.title,u.username,t.typename,n.pubtime from note n, users u, notetype t where n.user_id=u.id and n.typeid=t.typeid and n.title like ?";
        Object[] params = {title};
        rs = BaseCC.execute(conn, sql, params, ps, rs);
        List<notePageShow> noteList = new ArrayList<>();
        while(rs.next()){
            notePageShow note = new notePageShow();
            note.setNoteId(rs.getInt("note_id"));
            note.setTitle(rs.getString("title"));
            note.setTypename(rs.getString("typename"));
            note.setUsername(rs.getString("username"));
            note.setPubTime(rs.getTimestamp("pubtime"));
            noteList.add(note);
        }
        BaseCC.closeResource(conn,ps,rs);
        return noteList;
    }

    @Override
    public int deleteTypeById(int id) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        String sql = "delete from notetype where typeid=?";
        Object[] params = {id};
        int i = BaseCC.execute(conn, sql, params, ps);
        BaseCC.closeResource(conn,ps,null);
        return i;
    }

    @Override
    public int addType(String typeName) throws SQLException {
        Connection conn = BaseCC.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into notetype(typename) values(?)";
        Object[] params = {typeName};
        int i = BaseCC.execute(conn, sql, params, ps);
        BaseCC.closeResource(conn,ps,null);
        return i;
    }

}
